﻿using BCCommon;
using BCEntity.TMS.MoveHouseConfig.MoveHouseServiceConfig;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.TMS.MoveHouseConfig.MoveHouseServiceConfig
{
    public class MoveHouseServiceConfigData : IMoveHouseServiceConfigData
    {
        private readonly string SQL_QUERY_VIEW = @" SELECT 
                                              A.MoveHouseServiceConfigId,
                                              A.ServiceName,
                                              A.Remark,
                                              A.Enabled,
                                              A.CreateAdminId,
                                              A.CreateAdminName,
                                              A.CreateTime,
                                              A.UpdateTime,
                                              A.EditAdminId,
                                              A.EditAdminName
                                            FROM
                                              movehouseserviceconfig A ";
        private readonly string SQL_DELETE = @" DELETE FROM movehouseserviceconfig ";
        private readonly IDatabaseContext database;
        public MoveHouseServiceConfigData(IDatabaseContext database)
        {
            this.database = database;
        }
        public async Task<bool> IsExist(string serviceName, long? moveHouseServiceConfigId)
        {
            var sqlQuery = $" SELECT COUNT(1) FROM movehouseserviceconfig WHERE ServiceName=?ServiceName ";
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (moveHouseServiceConfigId.HasValue)
            {
                wheres.Add("MoveHouseServiceConfigId != ?MoveHouseServiceConfigId");
                parameters.Add("?MoveHouseServiceConfigId", moveHouseServiceConfigId.Value, DbType.Int64);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"AND {string.Join(" AND ", wheres)}";
            }
            var sql = $" {sqlQuery}{whereSql} ";
            return await this.database.Slave.QueryFirstOrDefaultAsync<int>(sql, new { serviceName, moveHouseServiceConfigId }) > 0;
        }
        public async Task<MoveHouseServiceConfigEntity> InsertAsync(MoveHouseServiceConfigEntity entity)
        {
            return await this.database.Master.InsertAsync(entity, database.Transaction);
        }
        public async Task<MoveHouseServiceConfigEntity> UpdateAsync(MoveHouseServiceConfigEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        public async Task<MoveHouseServiceConfigEntity> GetSingle(long moveHouseServiceConfigId)
        {
            var sql = typeof(MoveHouseServiceConfigEntity).GetSelectSqlByPrimaryKeys();
            return await this.database.Slave.QueryFirstOrDefaultAsync<MoveHouseServiceConfigEntity>(sql, new { moveHouseServiceConfigId });
        }
        public async Task<int> DeleteAsync(long moveHouseServiceConfigId)
        {
            var sql = $" {SQL_DELETE} WHERE MoveHouseServiceConfigId=?MoveHouseServiceConfigId ";
            return await this.database.Master.ExecuteAsync(sql, new { moveHouseServiceConfigId }, database.Transaction);
        }
        public async Task<IEnumerable<MoveHouseServiceConfigEntity>> GetList(bool? isEnabled)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sql = $"{SQL_QUERY_VIEW} {whereSql} GROUP BY A.MoveHouseServiceConfigId ORDER BY A.CreateTime DESC ";
            return await this.database.Slave.QueryAsync<MoveHouseServiceConfigEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<MoveHouseServiceConfigEntity>, int)> Query(bool? isEnabled, string serviceName, DateTime? createStartDate,
             DateTime? createEndDate, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (isEnabled.HasValue)
            {
                wheres.Add("A.Enabled = ?Enabled");
                parameters.Add("?Enabled", isEnabled.Value, DbType.Boolean);
            }
            if (!string.IsNullOrWhiteSpace(serviceName))
            {
                wheres.Add("A.ServiceName LIKE ?ServiceName");
                parameters.Add("?ServiceName", $"%{serviceName}%", DbType.AnsiString);
            }
            if (createStartDate.HasValue)
            {
                wheres.Add("A.CreateTime >= ?CreateStartDate");
                parameters.Add("?CreateStartDate", createStartDate.Value.Date, DbType.Date);
            }
            if (createEndDate.HasValue)
            {
                wheres.Add("A.CreateTime < ?CreateEndDate");
                parameters.Add("?CreateEndDate", createEndDate.Value.AddDays(1).Date, DbType.Date);
            }
            string whereSql = string.Empty;
            if (wheres.Count > 0)
            {
                whereSql = $"WHERE {string.Join(" AND ", wheres)}";
            }
            string sqlCount = $" SELECT COUNT(A.MoveHouseServiceConfigId) FROM movehouseserviceconfig A ";
            string sql = $@"{SQL_QUERY_VIEW} {whereSql} GROUP BY A.MoveHouseServiceConfigId ORDER BY A.CreateTime DESC 
                                LIMIT {(pageIndex - 1) * pageSize}, {pageSize};{sqlCount}{whereSql}";
            return await this.database.Slave.QueryPageableListAsync<MoveHouseServiceConfigEntity>(sql, parameters);
        }

    }
}
